package da;

import data.Tribe;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.util.ResourceBundle;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Stijn Verslycken, Jonas Van Den Bossche
 * @date 2007-2008
 */
public class TribeDA {

    private ResourceBundle bundle;
    private static TribeDA instance = null;

    private TribeDA() {
        bundle = ResourceBundle.getBundle("da.dbconsts");
    }

    public static TribeDA getInstance() {
        if (instance == null) {
            instance = new TribeDA();
        }
        return instance;
    }

    // <editor-fold desc="SELECTS" defaultstate="collapsed">
    
    /**
     * Fetches all known tribes
     * @return list of tribes
     */
    public Vector<Tribe> getAllTribes() {
        Vector<Tribe> tribes = null;
        Tribe tribe = null;

        String sql = bundle.getString("getAllTribes");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                ResultSet rs = prepstat.executeQuery();

                tribes = new Vector<Tribe>();
                while (rs.next()) {
                    tribe = new Tribe(rs.getInt("tribe_id"), rs.getString("name"),
                            rs.getString("description"), rs.getInt("chieftain_id"));
                    tribes.add(tribe);
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(TribeDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getAllTribes error -->" + ex.toString());
        }
        return tribes;

    }

    /**
     * Fetches a tribe by its id.
     * @param tribeId
     * @return tribe; indien niet gevonden null
     */
    public Tribe getTribeById(int tribeId) {
        Tribe tribe = null;

        String sql = bundle.getString("getTribeById");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setInt(1, tribeId);

                ResultSet rs = prepstat.executeQuery();
                while (rs.next()) {
                    tribe = new Tribe(rs.getInt("tribe_id"), rs.getString("name"),
                            rs.getString("description"), rs.getInt("chieftain_id"));
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(TribeDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getTribeById error -->" + ex.toString());
        }
        return tribe;

    }

    /**
     * Fetches a tribe by name.
     * Returns null if no tribe was found.
     * @param tribeName
     * @return tribe
     */
    public Tribe getTribeByName(String tribeName) {
        Tribe tribe = null;

        String sql = bundle.getString("getTribeByName");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setString(1, tribeName);

                ResultSet rs = prepstat.executeQuery();
                while (rs.next()) {
                    tribe = new Tribe(rs.getInt("tribe_id"), rs.getString("name"),
                            rs.getString("description"), rs.getInt("chieftain_id"));
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(TribeDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getTribeByName error -->" + ex.toString());
        }
        return tribe;

    }
    // </editor-fold>
    // <editor-fold desc="INSERTS" defaultstate="collapsed">
    /**
     * Adds a tribe to the DB
     * @param tribe
     */
    public void addTribe(Tribe tribe) {

        String sql = bundle.getString("addTribe");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                // Add data in table "tribe"
                prepstat.setString(1, tribe.getName());
                prepstat.setString(2, tribe.getDescription());
                prepstat.setInt(3, tribe.getChieftainId());
                prepstat.executeUpdate();

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("addTribe error -->" + ex.toString());
        }


    }

    // </editor-fold>
    // <editor-fold desc="UPDATES" defaultstate="collapsed">
    
    /**
     * Updates an existing tribe.
     * @param tribe
     */
    public void editTribe(Tribe tribe) {
        String sql = bundle.getString("editTribe");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                // Updaten van tribe tabel
                prepstat.setString(1, tribe.getName());
                prepstat.setString(2, tribe.getDescription());
                prepstat.setInt(3, tribe.getChieftainId());
                prepstat.setInt(4, tribe.getTribeId());
                prepstat.executeUpdate();

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(TribeDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("editTribe error -->" + ex.toString());
        }
    }
    
    /**
     * Resets the tribeId's (to 1, "no tribe") of users who are member of a given tribe.
     * @param tribeId
     */   
    public void resetTribeIdForMembersOf(int tribeId) {
        String sql = bundle.getString("resetTribeIdForMembersOfTribe");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                // Updates table "tribe"
                prepstat.setInt(1, tribeId);
                prepstat.executeUpdate();

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("resetTribeIdForMembersOf error -->" + ex.toString());
        }
    }
    
    // </editor-fold>
    // <editor-fold desc="DELETES" defaultstate="collapsed">
    /**
     * Removes the given tribe from the DB.
     * 
     * Caution: referals in the "tribemember" table are untouched! 
     * You should consider using methode "resetTribeIdForMembersOf" to fix this.
     * @param tribeId
     */
    public void removeTribe(int tribeId) {

        String sql = bundle.getString("removeTribe");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                // Removes data from table "tribe"
                prepstat.setInt(1, tribeId);
                prepstat.executeUpdate();

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(TribeDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("removeTribe error -->" + ex.toString());
        }
    }
    // </editor-fold>
}
